/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package projeto.classes;

import br.com.fatecpg.mdi.Conexão;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


public class ClienteAcessaDados {
    
    private Conexão conexao = new Conexão();
    private Connection conn;
    private PreparedStatement pstmt;
    
    public boolean incluir(ModeloCliente c) throws SQLException {
        String insertSql = "INSERT INTO cliente(nm_cliente, ic_cliente_fornecedor, cd_cnpj, cd_cpf, ds_endereco, cd_telefone, nm_email) " +
                "VALUES(?, ?, ?, ?, ?, ?, ?)";
        try {
            conn = conexao.getConexao();
            //conn = conexao.getConectar;
            pstmt = conn.prepareStatement(insertSql);
            pstmt.setString(1, c.getNome());
            pstmt.setInt(2, c.getClienteFornecedor());
            pstmt.setString(3, c.getCnpj());
            pstmt.setString(4, c.getCpf());
            pstmt.setString(5, c.getEndereco());
            pstmt.setInt(6, c.getTelefone());
            pstmt.setString(7, c.getEmail());
            
            return pstmt.execute();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            conexao.fecharConexao();
        }
    }
    
    public int alterar(ModeloCliente c) throws SQLException {
        String updateSql = "UPDATE cliente SET nm_cliente = ?, ic_cliente_fornecedor = ?, cd_cnpj = ?, cd_cpf = ?, ds_endereco = ?, cd_telefone = ?, nm_email = ? " +
               "WHERE id_cliente = ?";
        try {
            conn = conexao.getConexao();
            pstmt = conn.prepareStatement(updateSql);
            pstmt.setString(1, c.getNome());
            pstmt.setInt(2, c.getClienteFornecedor());
            pstmt.setString(3, c.getCnpj());
            pstmt.setString(4, c.getCpf());
            pstmt.setString(5, c.getEndereco());
            pstmt.setInt(6, c.getTelefone());
            pstmt.setString(7, c.getEmail());
            pstmt.setInt(8, c.getId());
            
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            conexao.fecharConexao();
        }
    }
    
    public ArrayList<ModeloCliente> consulta(String filtro) throws SQLException{
        ArrayList<ModeloCliente> lista = new ArrayList<ModeloCliente>();
        String selectSql = "SELECT * FROM cliente WHERE " + filtro;
        ResultSet retornoConsulta = null;
        
        try {
            conn = conexao.getConexao();
            pstmt = conn.prepareStatement(selectSql);
            retornoConsulta = pstmt.executeQuery();
            
            while (retornoConsulta.next()) {
                ModeloCliente c = new ModeloCliente();
                c.setId(retornoConsulta.getInt("id_cliente"));
                c.setNome(retornoConsulta.getString("nm_cliente"));
                c.setClienteFornecedor(retornoConsulta.getInt("ic_cliente_fornecedor"));
                c.setCnpj(retornoConsulta.getString("cd_cnpj"));
                c.setCpf(retornoConsulta.getString("cd_cpf"));
                c.setEndereco(retornoConsulta.getString("ds_endereco"));
                c.setTelefone(retornoConsulta.getInt("cd_telefone"));
                c.setEmail(retornoConsulta.getString("nm_email"));
                
                lista.add(c);
            }
            
            return lista;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (retornoConsulta != null) {
                retornoConsulta.close();
            }
            conexao.fecharConexao();
           
        }
    }
    
    public ArrayList<ModeloCliente> consultaTodos() throws SQLException{
        ArrayList<ModeloCliente> lista = new ArrayList<ModeloCliente>();
        String selectSql = "SELECT * FROM cliente ORDER BY nm_cliente ";
        ResultSet retornoConsulta = null;
        
        try {
            conn = conexao.getConexao();
            pstmt = conn.prepareStatement(selectSql);
            retornoConsulta = pstmt.executeQuery();
            
            while (retornoConsulta.next()) {
                ModeloCliente c = new ModeloCliente();
                c.setId(retornoConsulta.getInt("id_cliente"));
                c.setNome(retornoConsulta.getString("nm_cliente"));
                c.setClienteFornecedor(retornoConsulta.getInt("ic_cliente_fornecedor"));
                c.setCnpj(retornoConsulta.getString("cd_cnpj"));
                c.setCpf(retornoConsulta.getString("cd_cpf"));
                c.setEndereco(retornoConsulta.getString("ds_endereco"));
                c.setTelefone(retornoConsulta.getInt("cd_telefone"));
                c.setEmail(retornoConsulta.getString("nm_email"));
                
                lista.add(c);
            }
            
            return lista;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (retornoConsulta != null) {
                retornoConsulta.close();
            }
            conexao.fecharConexao();
           
        }
    }
    
    public int consultaMaxId() throws SQLException {
        String selectSql = "SELECT MAX(id_cliente) FROM cliente";
        ResultSet retornoConsulta = null;
        int maxID = 0;
        
        try {
            conn = conexao.getConexao();
            pstmt = conn.prepareStatement(selectSql);
            retornoConsulta = pstmt.executeQuery();
            
            if (retornoConsulta.next()) {
                maxID = retornoConsulta.getInt(1);
            }
            
            return maxID;
            
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (retornoConsulta != null) {
                retornoConsulta.close();
            }
            conexao.fecharConexao();
           
        }
    }
    
    public int deletar(int id) throws SQLException {
        String deleteSql = "DELETE FROM cliente WHERE id_cliente = ?";
        
        try {
            conn = conexao.getConexao();
            pstmt = conn.prepareStatement(deleteSql);
            pstmt.setInt(1, id);
            
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            conexao.fecharConexao();
           
        }
    }
}
